MySQL Commands
MySQL is an open-source relational database management system (RDBMS) widely used in web applications. The SQL part of mySQL stands for "S'tructured '''Q'uery 'L'anguage." Creating Connections and Databases Creating a MySQL Connection $con=mysqli_connect(website_name, username, password, database); Creating a Database $sql="CREATE DATABASE my_db"; Initializing a Database shell> mysql_install_db options Initializes the MySQL Database Administering a MySQL Server mysqladmin can be used to check server configuration and current status as well as drop and create databases: shell> mysqladmin options command command-arg [command-arg] Switches -p Enter password -u Changes user, MySQL keeps current user if user does not enter -u switch -h Selects host; by default, MySQL connects to the server on local host if -h switch is not entered Creating a Table/Table Functions Main Data Types Exact Numerical Data Types DECIMAL, INTEGER, SMALLINT, NUMERIC Approximate Numerical Data Types FLOAT, REAL, DOUBLE PRECISION Character/Other CHAR, VARCHAR, BINARY Identifier Qualifiers col_name The column from whichever table in the statement contains a column of that name tbl_name.col_name The column name from the table name of the default database db_name.tbl_name.col_name The column name from the table name of the database db_name Creating a Table CREATE TABLE TableName( name(number), ... ); Use NOT NULL to specify that the data type cannot be null Displaying Table/Database Information shell> mysqlshow options [tbl_name [col_name]] Can be used to quickly see existing databases, tables, or a table's columns or indexes shell> myisamchk options tbl_name ... Can be used on MyISAM tables (tables that have .MYD and .MYI files for storing data and indexes) Upgrading a Table shell> mysql_upgrade options Upgrades the '''mysql tables to the newest version and examines them for incompatibilities with the newest version Testing a Table mysqlcheck '''checks tables for errors shell> mysqlcheck test t Saving a File/Loading Data to a File tee Saves input and output from a session and appends it to a specified file shell> mysqlimport options db_name textfile1 ... mysqlimport takes the filename, removes the extension, and uses the filename as the name of the table in which to store the data Other -f or --force Can prevent mySQL from terminating a session after it encounters an error (use carefully) Error functions shell> perror options errorcode ... '''perror '''prints descriptions for system error codes shell> comp_err options comp_err creates the errmsg.sys file that mysqld uses to determine the error messages associated with different error codes Replace shell> replace from to to ... -- file_name file_name ... shell> replace from to to ... < file_name '''replace can change strings in places or in standard input Resolveip shell> resolveip options {host_name|ip-addr} ... resolveip resolves host names to IP addresses and vice versa Connecting to MySQL Using JDBC import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; //do not import com.mysql.jdbc.* if you don't want problems public class LoadDriver { public static void main(String[] args) { try { // newInstance() call is works around some broken Java implementations Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception e) { // error handling } } } Sources Suehring, Steve. MySQL Bible. New York: Wiley Publishing, Inc., 2002. Print. 4.4 MySQL Installation-Related Programs: http://dev.mysql.com/doc/refman/5.7/en/programs-installation.html 4.5 MySQL Client Programs: http://dev.mysql.com/doc/refman/5.7/en/programs-client.html 4.6 MySQL Administrative and Utility Programs: http://dev.mysql.com/doc/refman/5.7/en/programs-admin-utils.html 4.8 Miscellaneous Programs: http://dev.mysql.com/doc/refman/5.7/en/programs-miscellaneous.html 6.1 Connecting to MySQL Using the JDBC DriverManager Interface: http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-connect-drivermanager.html Category:MySQL Category:JDBC